﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using aier.dataaccess.model;
using aier.xrm.wcf;
using Dapper;
using Microsoft.Xrm.Sdk;

namespace aier.dataaccess.dapper
{
    public class AppointmentDal
    {
        public DapperHelper dapperHelper { get; set; }



        /// <summary>
        /// 根据负责人查询区域下拉列表
        /// </summary>
        /// <param name="ownerid">负责人id
        /// </param>
        /// <returns></returns>
        public IEnumerable<AppointmentView> QryDistrictandcountyList(Guid ownerid)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<AppointmentView>>(a =>
            {
                string sql = @"select distinct new_districtandcounty,new_marketname+'-'+new_districtandcountyName as new_districtandcountyName
                                             from dbo.AIER_APPOINTMENT_INFO /**where**/";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (ownerid != Guid.Empty && ownerid != null)
                {
                    sqlBuilder.Where("new_districtandcounty is not null and ownerid = @ownerid", new { ownerid = ownerid });
                }
                return a.Query<AppointmentView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }

        /// <summary>
        /// 分页查询查询拜访列表qryAppointmentList(int fromIndex, int toIndex, String sort, String ownerid)
        /// </summary>
        /// <param name="pageNum">页码</param>
        /// <param name="pageSize">每页信息条数</param>
        /// <param name="sort">排序方式（asc或desc）</param>
        /// <param name="ownerid">负责人id</param>
        /// <returns></returns>
        public IEnumerable<AppointmentView> QryAppointmentListPage(int pageNum, int pageSize
                , string sort, Guid ownerid, string starttime, string endtime)
        {
            pageNum = pageNum - 1;
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<AppointmentView>>(a =>
           {

               string sql = @" select   
                                       t1.activityid,
                                       t1.Subject,
                                       dbo.fn_UTCToTzCodeSpecificLocalTime(t1.ScheduledStart,220) ScheduledStart,
                                       dbo.fn_UTCToTzCodeSpecificLocalTime(t1.ScheduledEnd,220) ScheduledEnd,
                                       t1.new_visitahospital,
                                       t1.new_visitahospitalName,
                                       t1.new_visitpurpose,
                                       t1.new_signinplace,
                                       t1.new_descripion,
                                       t1.statecode,
                                       t1.OwnerId,
                                       p.PartyIdName as requiredattendeesName
                                       from dbo.Appointment t1  /**leftjoin**/ /**where**/ /**orderby**/";
               SqlBuilder sqlBuilder = new SqlBuilder();
               var sqlTemplate = sqlBuilder.AddTemplate(sql);
                //sqlBuilder.LeftJoin($"(select top {pageSize} row_number() OVER(ORDER BY ScheduledStart {sort} ) n, activityid from dbo.Appointment where OwnerId = '{ownerid}' and(statecode = 0 or statecode = 3)  ) t2 on t1.activityid = t2.activityid");            
                sqlBuilder.LeftJoin("dbo.ActivityParty p on p.ActivityId = t1.ActivityId and p.ParticipationTypeMask=5");
                //sqlBuilder.Where("t1.activityid = t2.activityid  and t2.n > @pageNum", new { pageNum= pageNum });
                //sqlBuilder.OrderBy(" t2.n asc");
                //sqlBuilder.Where(" (t1.statecode = 0 or t1.statecode = 3) ");
                if (!string.IsNullOrWhiteSpace(starttime) && !string.IsNullOrWhiteSpace(endtime))
               {
                   sqlBuilder.Where(" t1.ScheduledStart between  @starttime and @endtime", new { starttime = starttime, endtime = endtime });
               }
               sqlBuilder.Where("t1.OwnerId = @ownerid", new { ownerid = ownerid });
               sqlBuilder.OrderBy($"t1.ScheduledStart desc offset {pageNum}*{pageSize} rows fetch next {pageSize} rows only");
               return a.Query<AppointmentView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
           });
        }


        public IEnumerable<AppointmentView> QryAppointmentListPage(int pageNum, int pageSize
        , string sort, Guid ownerid, string starttime, string endtime, int? statecode)
        {
            pageNum = pageNum - 1;
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<AppointmentView>>(a =>
            {

                string sql = @" select   
                                       t1.activityid,
                                       t1.Subject,
                                       dbo.fn_UTCToTzCodeSpecificLocalTime(t1.ScheduledStart,220) ScheduledStart,
                                       dbo.fn_UTCToTzCodeSpecificLocalTime(t1.ScheduledEnd,220) ScheduledEnd,
                                       t1.new_visitahospital,
                                       t1.new_visitahospitalName,
                                       t1.new_visitpurpose,
                                       t1.new_signinplace,
                                       t1.new_descripion,
                                       t1.statecode,
                                       t1.OwnerId,
                                       p.PartyIdName as requiredattendeesName
                                       from dbo.Appointment t1  /**leftjoin**/ /**where**/ /**orderby**/";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                sqlBuilder.LeftJoin("dbo.ActivityParty p on p.ActivityId = t1.ActivityId and p.ParticipationTypeMask=5");

                if (!string.IsNullOrWhiteSpace(starttime) && !string.IsNullOrWhiteSpace(endtime))
                {
                    sqlBuilder.Where(" t1.ScheduledStart between  @starttime and @endtime", new { starttime = starttime, endtime = endtime });
                }
                if (statecode.HasValue)
                {
                    sqlBuilder.Where("t1.statecode  = @statecode", new { statecode = statecode.Value });
                }
                sqlBuilder.Where("t1.OwnerId = @ownerid", new { ownerid = ownerid });
                sqlBuilder.OrderBy($"t1.ScheduledStart desc offset {pageNum}*{pageSize} rows fetch next {pageSize} rows only");
                return a.Query<AppointmentView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }

        /// <summary>
        /// 根据区域id，状态，负责人查询拜访视图
        /// </summary>
        /// <param name="new_districtandcounty">区域id</param>
        /// <param name="statecode">状态</param>
        /// <param name="ownerid">负责人</param>
        /// <returns></returns>
        //public IEnumerable<AppointmentView> QryAppointmentList(Guid new_districtandcounty, int? statecode, Guid ownerid)
        //{
        //    return QryAppointmentList(new_districtandcounty, statecode, null, null, ownerid, Guid.Empty);
        //}

        /// <summary>
        /// 根据区域id，状态，时间，负责人查询拜访视图
        /// </summary>
        /// <param name="new_districtandcounty">区域id</param>
        /// <param name="statecode">状态</param>
        /// <param name="startDate">开始时间</param>
        /// <param name="endDate">结束时间</param>
        /// <param name="ownerid">负责人</param>
        /// <returns></returns>
        //public IEnumerable<AppointmentView> QryAppointmentList(Guid new_districtandcounty, int? statecode, string startDate
        //                        , string endDate, Guid ownerid) {
        //    return QryAppointmentList(new_districtandcounty, statecode, startDate, endDate, ownerid, Guid.Empty);
        //}

        /// <summary>
        /// 根据区域id，状态，时间，负责人，拜访对象查询拜访视图
        /// </summary>
        /// <param name="new_districtandcounty">区域id</param>
        /// <param name="statecode">状态</param>
        /// <param name="startDate">开始时间</param>
        /// <param name="endDate">结束时间</param>
        /// <param name="ownerid">负责人</param>
        /// <param name="contactId">拜访对象</param>
        /// <returns></returns>
        public IEnumerable<AppointmentView> QryAppointmentList(Guid new_districtandcounty, int? statecode, string startDate
                                , string endDate, Guid ownerid, Guid contactId, int pageNum, int pageSize)
        {
            pageNum = pageNum - 1;
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<AppointmentView>>(a =>
            {
                string sql = @"select distinct activityid, Subject, ScheduledStart, ScheduledEnd, new_visitahospital, new_visitahospitalName, 
                                                 new_visitpurpose, new_signinplace, new_signintime, new_descripion, statecode, new_province, 
                                                 new_provinceName, new_market, new_marketname, new_districtandcounty, new_districtandcountyName, 
                                                 new_community, new_communityName, requiredattendees, requiredattendeesName, 
                                                 OwnerId,CONVERT(varchar(100), ScheduledStart, 120) as scheduledstartstring 
                                                 from AIER_APPOINTMENT_INFO  /**where**/ /**orderby**/";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (statecode != null)
                {
                    sqlBuilder.Where("statecode = @statecode", new { statecode = statecode });
                }
                if (!string.IsNullOrWhiteSpace(startDate) && !string.IsNullOrWhiteSpace(endDate))
                {
                    sqlBuilder.Where(" (ScheduledStart between @startDate AND  @endDate)", new { startDate = startDate, endDate = endDate });
                }
                //if (!string.IsNullOrWhiteSpace(endDate))
                //{
                //    sqlBuilder.Where(" CONVERT(varchar(100), ScheduledEnd, 23) <= @endDate", new { endDate = endDate });
                //}      
                if (ownerid != Guid.Empty && ownerid != null)
                {
                    sqlBuilder.Where("OwnerId = @ownerid", new { ownerid = ownerid });
                }
                if (contactId != Guid.Empty && contactId != null)
                {
                    sqlBuilder.Where("requiredattendees = @contactId", new { contactId = contactId });
                }
                if (new_districtandcounty != Guid.Empty)
                {
                    sqlBuilder.Where("new_districtandcounty = @new_districtandcounty", new { new_districtandcounty = new_districtandcounty });
                }
                sqlBuilder.OrderBy($"ScheduledStart desc offset {pageNum}*{pageSize} rows fetch next {pageSize} rows only");
                return a.Query<AppointmentView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }

        /// <summary>
        /// 根据区域id，状态，时间，负责人，拜访对象查询拜访视图
        /// </summary>
        /// <param name="new_districtandcounty">区域id</param>
        /// <param name="statecode">状态</param>
        /// <param name="startDate">开始时间</param>
        /// <param name="endDate">结束时间</param>
        /// <param name="ownerid">负责人</param>
        /// <param name="contactId">拜访对象</param>
        /// <returns></returns>
        public IEnumerable<AppointmentView> QryAppointmentListSecond(Guid new_districtandcounty, int? statecode, string startDate
                                , string endDate, Guid ownerid, Guid contactId, int pageNum, int pageSize)
        {
            pageNum = pageNum - 1;
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<AppointmentView>>(a =>
            {
                string sql = @"select  
                                A.activityid,               --拜访ID               
                                A.Subject,                  --主题
                                dbo.fn_UTCToTzCodeSpecificLocalTime(A.ScheduledStart,220)ScheduledStart,
                                dbo.fn_UTCToTzCodeSpecificLocalTime(A.ScheduledEnd,220)ScheduledEnd,
                                A.new_visitahospital,       --拜访医院ID
                                A.new_visitahospitalName,   --拜访医院
                                A.new_visitpurpose,         --拜访目的
                                A.new_signinplace,          --签到地点
                                dbo.fn_UTCToTzCodeSpecificLocalTime(A.new_signintime,220) new_signintime,           --签到时间
                                A.new_descripion,           --拜访目的达成情况
                                A.statecode,                --状态 0:开启，1：已完成，2：已取消，3：进行中
                                p.PartyId as requiredattendees,        --拜访对象ID
                                p.PartyIdName as requiredattendeesName,--拜访对象ID
                                A.OwnerId                              --拜访负责人ID
                                from Appointment A with(nolock)
                                left join ActivityPartyBase p with(nolock) on p.ActivityId = a.ActivityId and p.ParticipationTypeMask=5
                             
                                /**where**/ /**orderby**/";
                //   left join Contactbase c with(nolock) on c.ContactId = p.PartyId
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (statecode != null)
                {
                    sqlBuilder.Where("A.statecode = @statecode", new { statecode = statecode });
                }
                if (!string.IsNullOrWhiteSpace(startDate) && !string.IsNullOrWhiteSpace(endDate))
                {
                    DateTime start = DateTime.Parse(startDate).ToUniversalTime();
                    DateTime end = DateTime.Parse(endDate).ToUniversalTime();
                    sqlBuilder.Where("A.ScheduledStart between @startDate AND  @endDate", new { startDate = start, endDate = end });
                }  
                if (ownerid != Guid.Empty && ownerid != null)
                {
                    sqlBuilder.Where("A.OwnerId = @ownerid", new { ownerid = ownerid });
                }
                if (contactId != Guid.Empty && contactId != null)
                {
                    sqlBuilder.Where("p.PartyId = @contactId", new { contactId = contactId });
                }
                if (new_districtandcounty != Guid.Empty)
                {
                    //sqlBuilder.Where("c.new_districtandcounty = @new_districtandcounty", new { new_districtandcounty = new_districtandcounty });
                    sqlBuilder.Where("exists (select ContactId from Contactbase as c where c.ContactId=p.PartyId and new_districtandcounty= @new_districtandcounty) ", new { new_districtandcounty = new_districtandcounty });
                }
                sqlBuilder.OrderBy($"A.ScheduledStart desc offset {pageNum}*{pageSize} rows fetch next {pageSize} rows only");
                return a.Query<AppointmentView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }

        /// <summary>
        /// 根据区域id，状态，时间，负责人，拜访对象查询拜访视图
        /// </summary>
        /// <param name="new_districtandcounty">区域id</param>
        /// <param name="statecode">状态</param>
        /// <param name="startDate">开始时间</param>
        /// <param name="endDate">结束时间</param>
        /// <param name="ownerid">负责人</param>
        /// <param name="contactId">拜访对象</param>
        /// <returns></returns>
        public IEnumerable<AppointmentView> QryAppointmentListSecondV2(Guid new_districtandcounty, int? statecode, string startDate
                                , string endDate, Guid ownerid, Guid contactId, int pageNum, int pageSize)
        {
            pageNum = pageNum - 1;
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<AppointmentView>>(a =>
            {
                string sql = @"select  
                                A.activityid,               --拜访ID               
                                A.Subject,                  --主题
                                dbo.fn_UTCToTzCodeSpecificLocalTime(A.ScheduledStart,220)ScheduledStart,
                                dbo.fn_UTCToTzCodeSpecificLocalTime(A.ScheduledEnd,220)ScheduledEnd,
                                A.new_visitahospital,       --拜访医院ID
                                A.new_visitahospitalName,   --拜访医院
                                A.new_visitpurpose,         --拜访目的
                                A.new_signinplace,          --签到地点
                                dbo.fn_UTCToTzCodeSpecificLocalTime(A.new_signintime,220) new_signintime,           --签到时间
                                A.new_descripion,           --拜访目的达成情况
                                A.statecode,                --状态 0:开启，1：已完成，2：已取消，3：进行中
                                p.PartyId as requiredattendees,        --拜访对象ID
                                p.PartyIdName as requiredattendeesName,--拜访对象ID
                                A.OwnerId                              --拜访负责人ID
                                from Appointment A with(nolock)
                                left join ActivityPartyBase p with(nolock) on p.ActivityId = a.ActivityId and p.ParticipationTypeMask=5
                             
                                /**where**/ /**orderby**/";
                //   left join Contactbase c with(nolock) on c.ContactId = p.PartyId
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (statecode != null)
                {
                    sqlBuilder.Where("A.statecode = @statecode", new { statecode = statecode });
                }
                else {
                    sqlBuilder.Where("A.statecode in(0,3)");
                }

                if (!string.IsNullOrWhiteSpace(startDate) && !string.IsNullOrWhiteSpace(endDate))
                {
                    DateTime start = DateTime.Parse(startDate).ToUniversalTime();
                    DateTime end = DateTime.Parse(endDate).ToUniversalTime();
                    sqlBuilder.Where("A.ScheduledStart between @startDate AND  @endDate", new { startDate = start, endDate = end });
                }
                if (ownerid != Guid.Empty && ownerid != null)
                {
                    sqlBuilder.Where("A.OwnerId = @ownerid", new { ownerid = ownerid });
                }
                if (contactId != Guid.Empty && contactId != null)
                {
                    sqlBuilder.Where("p.PartyId = @contactId", new { contactId = contactId });
                }
                if (new_districtandcounty != Guid.Empty)
                {
                    //sqlBuilder.Where("c.new_districtandcounty = @new_districtandcounty", new { new_districtandcounty = new_districtandcounty });
                    sqlBuilder.Where("exists (select ContactId from Contactbase as c where c.ContactId=p.PartyId and new_districtandcounty= @new_districtandcounty) ", new { new_districtandcounty = new_districtandcounty });
                }
                sqlBuilder.OrderBy($"A.ScheduledStart desc offset {pageNum}*{pageSize} rows fetch next {pageSize} rows only");
                return a.Query<AppointmentView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }


        /// <summary>
        /// 查询拜访
        /// </summary>
        /// <param name="activityId">拜访id</param>
        /// <returns></returns>
        public AppointmentView QryAppointment(Guid activityId)
        {
            return dapperHelper.ExecueWithoutTransaction<AppointmentView>(a =>
            {
                string sql = @"select  distinct 
                                            A.activityid,               --拜访ID               
                                            A.Subject,                  --主题
                                            dbo.fn_UTCToTzCodeSpecificLocalTime(A.ScheduledStart,220)ScheduledStart,
                                            dbo.fn_UTCToTzCodeSpecificLocalTime(A.ScheduledEnd,220)ScheduledEnd,
                                            --A.ScheduledStart,           --计划拜访开始时间
                                            --A.ScheduledEnd,             --计划拜访结束时间
                                            A.new_visitahospital,       --拜访医院ID
                                            A.new_visitahospitalName,   --拜访医院
                                            A.new_visitpurpose,         --拜访目的
                                            A.new_signinplace,          --签到地点
                                            dbo.fn_UTCToTzCodeSpecificLocalTime(A.new_signintime,220) new_signintime,           --签到时间
                                            A.new_descripion,           --拜访目的达成情况
                                            A.statecode,                --状态 0:开启，1：已完成，2：已取消，3：进行中
                                            c.new_province,
                                            c.new_provinceName,
                                            c.new_market,
                                            c.new_marketname,
                                            c.new_districtandcounty,
                                            c.new_districtandcountyName,
                                            c.new_community,
                                            c.new_communityName,
                                            p.PartyId as requiredattendees,        --拜访对象ID
                                            p.PartyIdName as requiredattendeesName,--拜访对象ID
                                            A.OwnerId                              --拜访负责人ID
                                            from Appointment A with(nolock)
                                            left join ActivityParty p with(nolock) on p.ActivityId = a.ActivityId and p.ParticipationTypeMask=5
                                            left join Contact c with(nolock) on c.ContactId = p.PartyId /**where**/";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (activityId != Guid.Empty)
                {
                    sqlBuilder.Where("a.ActivityId = @activityId", new { activityId = activityId });
                }
                return a.QueryFirst<AppointmentView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }

        /// <summary>
        /// 根据计划拜访时间查询
        /// </summary>
        /// <param name="starttime">计划拜访开始时间</param>
        /// <param name="endtime">计划拜访结束时间</param>
        /// <returns></returns>
        public IEnumerable<AppointmentView> QueryAppointmentByTime(string starttime, string endtime)
        {
            //List<AppointmentView> appointments = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            return dapperHelper.ExecueWithoutTransaction<IEnumerable<AppointmentView>>(a =>
             {
                 string sql = @"select scheduledStart,subject,activityid from AIER_APPOINTMENT_INFO s /**where**/";

                 var sqlTemplate = sqlBuilder.AddTemplate(sql);
                 sqlBuilder.Where(" s.ScheduledStart between @ScheduledStart and @ScheduledEnd ");
                 sqlParams.Add("@ScheduledStart", starttime);
                 sqlParams.Add("@ScheduledEnd", endtime);

                 return a.Query<AppointmentView>(sqlTemplate.RawSql, sqlParams);
             });


        }

        public int QueryApppointmentCountV2(Guid new_districtandcounty, int? statecode, string startDate
                                , string endDate, Guid ownerid, Guid contactId)
        {
            int total = 0;

            //获取用户信息
            new DapperHelper().ExecueWithoutTransaction(a =>
            {
                string sql = @"select  
                                count(1)                              --拜访负责人ID
                                from Appointment A with(nolock)
                                left join ActivityPartyBase p with(nolock) on p.ActivityId = a.ActivityId and p.ParticipationTypeMask=5
                             
                                /**where**/ /**orderby**/";
                //   left join Contactbase c with(nolock) on c.ContactId = p.PartyId
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (statecode != null)
                {
                    sqlBuilder.Where("A.statecode = @statecode", new { statecode = statecode });
                }
                else {
                    sqlBuilder.Where("A.statecode in(0,3)");
                }

                if (!string.IsNullOrWhiteSpace(startDate) && !string.IsNullOrWhiteSpace(endDate))
                {
                    sqlBuilder.Where(" (A.ScheduledStart between @startDate AND  @endDate)", new { startDate = startDate, endDate = endDate });
                }
                if (ownerid != Guid.Empty && ownerid != null)
                {
                    sqlBuilder.Where("A.OwnerId = @ownerid", new { ownerid = ownerid });
                }
                if (contactId != Guid.Empty && contactId != null)
                {
                    sqlBuilder.Where("p.PartyId = @contactId", new { contactId = contactId });
                }
                if (new_districtandcounty != Guid.Empty)
                {
                    //sqlBuilder.Where("c.new_districtandcounty = @new_districtandcounty", new { new_districtandcounty = new_districtandcounty });
                    sqlBuilder.Where("exists (select ContactId from Contactbase as c where c.ContactId=p.PartyId and new_districtandcounty= @new_districtandcounty) ", new { new_districtandcounty = new_districtandcounty });
                }

                object objValue = a.ExecuteScalar(sqlTemplate.RawSql, sqlTemplate.Parameters);
                if (objValue != null) total = int.Parse(objValue.ToString());
            });

            return total;
        }


        public int QueryApppointmentCount(Guid new_districtandcounty, int? statecode, string startDate
                                , string endDate, Guid ownerid, Guid contactId) {
            int total = 0;

            //获取用户信息
            new DapperHelper().ExecueWithoutTransaction(a =>
            {
                string sql = @"select  
                                count(1)                              --拜访负责人ID
                                from Appointment A with(nolock)
                                left join ActivityPartyBase p with(nolock) on p.ActivityId = a.ActivityId and p.ParticipationTypeMask=5
                             
                                /**where**/ /**orderby**/";
                //   left join Contactbase c with(nolock) on c.ContactId = p.PartyId
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (statecode != null)
                {
                    sqlBuilder.Where("A.statecode = @statecode", new { statecode = statecode });
                }
                if (!string.IsNullOrWhiteSpace(startDate) && !string.IsNullOrWhiteSpace(endDate))
                {
                    sqlBuilder.Where(" (A.ScheduledStart between @startDate AND  @endDate)", new { startDate = startDate, endDate = endDate });
                }
                if (ownerid != Guid.Empty && ownerid != null)
                {
                    sqlBuilder.Where("A.OwnerId = @ownerid", new { ownerid = ownerid });
                }
                if (contactId != Guid.Empty && contactId != null)
                {
                    sqlBuilder.Where("p.PartyId = @contactId", new { contactId = contactId });
                }
                if (new_districtandcounty != Guid.Empty)
                {
                    //sqlBuilder.Where("c.new_districtandcounty = @new_districtandcounty", new { new_districtandcounty = new_districtandcounty });
                    sqlBuilder.Where("exists (select ContactId from Contactbase as c where c.ContactId=p.PartyId and new_districtandcounty= @new_districtandcounty) ", new { new_districtandcounty = new_districtandcounty });
                }

                object objValue = a.ExecuteScalar(sqlTemplate.RawSql, sqlTemplate.Parameters);
                if (objValue != null) total = int.Parse(objValue.ToString());
            });

            return total;
        }

    }
}
